﻿//------------------------------------------------------------------------------
// 创建标识: Copyright (C) 2009 Socansoft.com 版权所有
// 创建描述: SocanCode代码生成器自动创建于 2009-2-11 11:38:42
//
// 功能描述: 
//
// 修改标识: 
// 修改描述: 
//------------------------------------------------------------------------------

using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using IDAL;
using DBUtility;
using System.Collections;
using System.Collections.Generic;

namespace SQLServerDAL
{
    /// <summary>
    /// 数据访问类 Ad
    /// </summary>
    public class Ad : IAd
    {
        public Ad()
        { }

        #region  成员方法
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public string Add(Model.Ad model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into [Ad](");
            strSql.Append("[Adid],[AdName],[AdPicAddress],[AdLink],[AdPicUrl],[AdPicStartDateTime],[AdPicEndDateTim],[IsRunState],[AdAttribute1],[AdAttribute2],[AdAttribute3],[AdAttribute4],[AdAttribute5],[AdAttribute6],[AdAttribute7],[AdAttribute8])");
            strSql.Append(" values (");
            strSql.Append("@Adid,@AdName,@AdPicAddress,@AdLink,@AdPicUrl,@AdPicStartDateTime,@AdPicEndDateTim,@IsRunState,@AdAttribute1,@AdAttribute2,@AdAttribute3,@AdAttribute4,@AdAttribute5,@AdAttribute6,@AdAttribute7,@AdAttribute8)");
            SqlParameter[] parameters = {
                    new SqlParameter("@Adid", SqlDbType.VarChar,50),
                    new SqlParameter("@AdName", SqlDbType.VarChar,50),
                    new SqlParameter("@AdPicAddress", SqlDbType.VarChar,200),
                    new SqlParameter("@AdLink", SqlDbType.VarChar,200),
                    new SqlParameter("@AdPicUrl", SqlDbType.VarChar,200),
                    new SqlParameter("@AdPicStartDateTime", SqlDbType.DateTime),
                    new SqlParameter("@AdPicEndDateTim", SqlDbType.DateTime),
                    new SqlParameter("@IsRunState", SqlDbType.Int),
                    new SqlParameter("@AdAttribute1", SqlDbType.VarChar,50),
                    new SqlParameter("@AdAttribute2", SqlDbType.VarChar,50),
                    new SqlParameter("@AdAttribute3", SqlDbType.VarChar,1000),
                    new SqlParameter("@AdAttribute4", SqlDbType.VarChar,1000),
                    new SqlParameter("@AdAttribute5", SqlDbType.Int),
                    new SqlParameter("@AdAttribute6", SqlDbType.Int),
                    new SqlParameter("@AdAttribute7", SqlDbType.Decimal),
                    new SqlParameter("@AdAttribute8", SqlDbType.Decimal)
            };
            parameters[0].Value = model.Adid;

            if (model.AdName != null)
                parameters[1].Value = model.AdName;
            else
                parameters[1].Value = DBNull.Value;


            if (model.AdPicAddress != null)
                parameters[2].Value = model.AdPicAddress;
            else
                parameters[2].Value = DBNull.Value;


            if (model.AdLink != null)
                parameters[3].Value = model.AdLink;
            else
                parameters[3].Value = DBNull.Value;


            if (model.AdPicUrl != null)
                parameters[4].Value = model.AdPicUrl;
            else
                parameters[4].Value = DBNull.Value;


            if (model.AdPicStartDateTime != DateTime.MinValue)
                parameters[5].Value = model.AdPicStartDateTime;
            else
                parameters[5].Value = DBNull.Value;


            if (model.AdPicEndDateTim != DateTime.MinValue)
                parameters[6].Value = model.AdPicEndDateTim;
            else
                parameters[6].Value = DBNull.Value;

            parameters[7].Value = model.IsRunState;

            if (model.AdAttribute1 != null)
                parameters[8].Value = model.AdAttribute1;
            else
                parameters[8].Value = DBNull.Value;


            if (model.AdAttribute2 != null)
                parameters[9].Value = model.AdAttribute2;
            else
                parameters[9].Value = DBNull.Value;


            if (model.AdAttribute3 != null)
                parameters[10].Value = model.AdAttribute3;
            else
                parameters[10].Value = DBNull.Value;


            if (model.AdAttribute4 != null)
                parameters[11].Value = model.AdAttribute4;
            else
                parameters[11].Value = DBNull.Value;

            parameters[12].Value = model.AdAttribute5;
            parameters[13].Value = model.AdAttribute6;
            parameters[14].Value = model.AdAttribute7;
            parameters[15].Value = model.AdAttribute8;

            if (SqlHelper.ExecuteSql(SqlHelper.LocalSqlServer, strSql.ToString(), parameters) > 0)
                return model.Adid;
            else
                return null;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.Ad model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update [Ad] set ");
            strSql.Append("[AdName]=@AdName,");
            strSql.Append("[AdPicAddress]=@AdPicAddress,");
            strSql.Append("[AdLink]=@AdLink,");
            strSql.Append("[AdPicUrl]=@AdPicUrl,");
            strSql.Append("[AdPicStartDateTime]=@AdPicStartDateTime,");
            strSql.Append("[AdPicEndDateTim]=@AdPicEndDateTim,");
            strSql.Append("[IsRunState]=@IsRunState,");
            strSql.Append("[AdAttribute1]=@AdAttribute1,");
            strSql.Append("[AdAttribute2]=@AdAttribute2,");
            strSql.Append("[AdAttribute3]=@AdAttribute3,");
            strSql.Append("[AdAttribute4]=@AdAttribute4,");
            strSql.Append("[AdAttribute5]=@AdAttribute5,");
            strSql.Append("[AdAttribute6]=@AdAttribute6,");
            strSql.Append("[AdAttribute7]=@AdAttribute7,");
            strSql.Append("[AdAttribute8]=@AdAttribute8");
            strSql.Append(" where [Adid]=@Adid");
            SqlParameter[] parameters = {
                    new SqlParameter("@Adid", SqlDbType.VarChar,50),
                    new SqlParameter("@AdName", SqlDbType.VarChar,50),
                    new SqlParameter("@AdPicAddress", SqlDbType.VarChar,200),
                    new SqlParameter("@AdLink", SqlDbType.VarChar,200),
                    new SqlParameter("@AdPicUrl", SqlDbType.VarChar,200),
                    new SqlParameter("@AdPicStartDateTime", SqlDbType.DateTime),
                    new SqlParameter("@AdPicEndDateTim", SqlDbType.DateTime),
                    new SqlParameter("@IsRunState", SqlDbType.Int),
                    new SqlParameter("@AdAttribute1", SqlDbType.VarChar,50),
                    new SqlParameter("@AdAttribute2", SqlDbType.VarChar,50),
                    new SqlParameter("@AdAttribute3", SqlDbType.VarChar,1000),
                    new SqlParameter("@AdAttribute4", SqlDbType.VarChar,1000),
                    new SqlParameter("@AdAttribute5", SqlDbType.Int),
                    new SqlParameter("@AdAttribute6", SqlDbType.Int),
                    new SqlParameter("@AdAttribute7", SqlDbType.Decimal),
                    new SqlParameter("@AdAttribute8", SqlDbType.Decimal)
            };
            parameters[0].Value = model.Adid;

            if (model.AdName != null)
                parameters[1].Value = model.AdName;
            else
                parameters[1].Value = DBNull.Value;


            if (model.AdPicAddress != null)
                parameters[2].Value = model.AdPicAddress;
            else
                parameters[2].Value = DBNull.Value;


            if (model.AdLink != null)
                parameters[3].Value = model.AdLink;
            else
                parameters[3].Value = DBNull.Value;


            if (model.AdPicUrl != null)
                parameters[4].Value = model.AdPicUrl;
            else
                parameters[4].Value = DBNull.Value;


            if (model.AdPicStartDateTime != DateTime.MinValue)
                parameters[5].Value = model.AdPicStartDateTime;
            else
                parameters[5].Value = DBNull.Value;


            if (model.AdPicEndDateTim != DateTime.MinValue)
                parameters[6].Value = model.AdPicEndDateTim;
            else
                parameters[6].Value = DBNull.Value;

            parameters[7].Value = model.IsRunState;

            if (model.AdAttribute1 != null)
                parameters[8].Value = model.AdAttribute1;
            else
                parameters[8].Value = DBNull.Value;


            if (model.AdAttribute2 != null)
                parameters[9].Value = model.AdAttribute2;
            else
                parameters[9].Value = DBNull.Value;


            if (model.AdAttribute3 != null)
                parameters[10].Value = model.AdAttribute3;
            else
                parameters[10].Value = DBNull.Value;


            if (model.AdAttribute4 != null)
                parameters[11].Value = model.AdAttribute4;
            else
                parameters[11].Value = DBNull.Value;

            parameters[12].Value = model.AdAttribute5;
            parameters[13].Value = model.AdAttribute6;
            parameters[14].Value = model.AdAttribute7;
            parameters[15].Value = model.AdAttribute8;

            return SqlHelper.ExecuteSql(SqlHelper.LocalSqlServer, strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string Adid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete [Ad] ");
            strSql.Append(" where [Adid]=@Adid");
            SqlParameter[] parameters = {
                    new SqlParameter("@Adid", SqlDbType.VarChar,50)};
            parameters[0].Value = Adid;

            return SqlHelper.ExecuteSql(SqlHelper.LocalSqlServer, strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string Adid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from [Ad]");
            strSql.Append(" where [Adid]= @Adid");
            SqlParameter[] parameters = {
                    new SqlParameter("@Adid", SqlDbType.VarChar,50)};
            parameters[0].Value = Adid;
            return SqlHelper.Exists(SqlHelper.LocalSqlServer, strSql.ToString(), parameters);
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.Ad GetModel(string Adid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from [Ad] ");
            strSql.Append(" where [Adid]=@Adid");
            SqlParameter[] parameters = {
                    new SqlParameter("@Adid", SqlDbType.VarChar,50)};
            parameters[0].Value = Adid;
            DataSet ds = SqlHelper.Query(SqlHelper.LocalSqlServer, strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow r = ds.Tables[0].Rows[0];
                return GetModel(r);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获取数据集,建议只在多表联查时使用
        /// </summary>
        public DataSet GetDataSet(string strSql)
        {
            return SqlHelper.Query(SqlHelper.LocalSqlServer, strSql);
        }

        /// <summary>
        /// 获取泛型数据列表,建议只在多表联查时使用
        /// </summary>
        public IList<Model.Ad> GetList(string strSql)
        {
            return GetList(GetDataSet(strSql));
        }

        /// <summary>
        /// 获取数据集,在单表查询时使用
        /// </summary>
        public DataSet GetDataSet(string strWhat, string strWhere, string strOrderby)
        {
            if (string.IsNullOrEmpty(strWhat))
                strWhat = "*";
            StringBuilder strSql = new StringBuilder("select " + strWhat + " from [Ad]");
            if (!string.IsNullOrEmpty(strWhere))
                strSql.Append(" where " + strWhere);
            if (!string.IsNullOrEmpty(strOrderby))
                strSql.Append(" order by " + strOrderby);
            return SqlHelper.Query(SqlHelper.LocalSqlServer, strSql.ToString());
        }

        /// <summary>
        /// 获取泛型数据列表,在单表查询时使用
        /// </summary>
        public IList<Model.Ad> GetList(string strWhat, string strWhere, string strOrderby)
        {
            return GetList(GetDataSet(strWhat, strWhere, strOrderby));
        }

        /// <summary>
        /// 分页获取泛型数据列表
        /// </summary>
        public IList<Model.Ad> GetList(int pageSize, int pageIndex, string fldSort, bool sort, string strCondition, out int pageCount, out int count)
        {
            string strSql;
            DataSet ds = SqlHelper.PageList(SqlHelper.LocalSqlServer, "[Ad]", null, pageSize, pageIndex, fldSort, sort, strCondition, "Adid", false, out pageCount, out count, out strSql);
            return GetList(ds);
        }
        #endregion

        /// <summary>
        /// 由一行数据得到一个实体
        /// </summary>
        private Model.Ad GetModel(DataRow r)
        {
            Model.Ad model = new Model.Ad();
            model.Adid = SqlHelper.GetString(r["Adid"]);
            model.AdName = SqlHelper.GetString(r["AdName"]);
            model.AdPicAddress = SqlHelper.GetString(r["AdPicAddress"]);
            model.AdLink = SqlHelper.GetString(r["AdLink"]);
            model.AdPicUrl = SqlHelper.GetString(r["AdPicUrl"]);
            model.AdPicStartDateTime = SqlHelper.GetDateTime(r["AdPicStartDateTime"]);
            model.AdPicEndDateTim = SqlHelper.GetDateTime(r["AdPicEndDateTim"]);
            model.IsRunState = SqlHelper.GetInt(r["IsRunState"]);
            model.AdAttribute1 = SqlHelper.GetString(r["AdAttribute1"]);
            model.AdAttribute2 = SqlHelper.GetString(r["AdAttribute2"]);
            model.AdAttribute3 = SqlHelper.GetString(r["AdAttribute3"]);
            model.AdAttribute4 = SqlHelper.GetString(r["AdAttribute4"]);
            model.AdAttribute5 = SqlHelper.GetInt(r["AdAttribute5"]);
            model.AdAttribute6 = SqlHelper.GetInt(r["AdAttribute6"]);
            model.AdAttribute7 = SqlHelper.GetDecimal(r["AdAttribute7"]);
            model.AdAttribute8 = SqlHelper.GetDecimal(r["AdAttribute8"]);
            return model;
        }

        /// <summary>
        /// 由数据集得到泛型数据列表
        /// </summary>
        private IList<Model.Ad> GetList(DataSet ds)
        {
            List<Model.Ad> l = new List<Model.Ad>();
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                l.Add(GetModel(r));
            }
            return l;
        }
    }
}
